package com.frame.database.session;

import java.io.BufferedReader;
import java.io.Serializable;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.*;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import oracle.sql.BLOB;
import oracle.sql.CLOB;
import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.Transaction;
import com.frame.database.*;
import com.frame.database.dataconver.DataConversionFactory;
import com.frame.database.operate.Jsession;
import com.frame.database.util.Seq;
import com.frame.hibernate.HibernateUtil;
import com.frame.util.BeanUtils;
import com.frame.util.StreamUtils;

/**
 * @Title: 系统
 * @Description:
 * @Copyright: Copyright (c) 2012
 * @Company:
 * @author 王海锋
 * @CreatedTime:2012-6-10 上午10:48:26
 * @version 1.0
 */

public class JdbcSession implements Jsession{

    private List<PreparedStatement> preparedStatementList;
    private List<Statement> statementList;
    private List<ResultSet> resultSetList;
    private String ftg;
    private boolean loadLob=false;
    private Connection conn;

    public JdbcSession(){

    }
    public JdbcSession(String ftg){
        this.ftg=ftg;
    }
    public List<JdataBean> query2(String sql,List parmList) throws Exception{
        Object[] obj=null;
        if(parmList!=null){
            obj=new Object[parmList.size()];
            for(int i=0;i<parmList.size();i++)
                obj[i]=parmList.get(i);
        }
        return query(sql, obj);
    }
    public List<JdataBean> query(String sql,Object[] parmObj) throws Exception{
        List resultList=new ArrayList();
        PreparedStatement ps=createPreparedStatement(sql);
        if(parmObj!=null){
            for(int i=0;i<parmObj.length;i++){
                if(parmObj[i] instanceof java.util.Date){
                    java.util.Date date=(java.util.Date)parmObj[i];
                    ps.setTimestamp(i+1, new Timestamp(date.getTime()));
                }else
                    ps.setObject(i+1, parmObj[i]);
            }
        }
        ResultSet rs=ps.executeQuery();
        try{
            ResultSetMetaData rsmd = rs.getMetaData();
            while(rs.next()){
                JdataBean bean=new JdataBean();
                for (int i=0;i<rsmd.getColumnCount();i++){
                    String colname=rsmd.getColumnLabel(i+1);
                    Object obj=rs.getObject(colname);
                    Object value=obj;
                    if(isLoadLob()){
                        if (obj instanceof BLOB || obj instanceof Blob){
                            if(obj!=null){
                                BLOB bolb=(BLOB)obj;
                                value=StreamUtils.streamToByte(bolb.getBinaryStream());
                            }
                        }else if(obj instanceof CLOB || obj instanceof Clob){
                            if(obj!=null){
                                CLOB clob=(CLOB)obj;
                                String objv="";
                                BufferedReader cb = new BufferedReader(clob.getCharacterStream());
                                String str = "";
                                while ((str = cb.readLine()) != null) {
                                    objv = objv.concat(str);  //最后以String的形式得到
                                }
                                value=objv;
                            }
                        }
                    }
                    //以大写字段名保存字段键值
                    bean.put(colname,value);
                }
                resultList.add(bean);
            }
        }catch(Exception  e){throw e;}finally{
            if(rs!=null)
                try{rs.close();}catch(Exception ee){ee.printStackTrace();}
        }
        return resultList;
    }
    public List<JdataBean> query2(String sql,List parmList,Class dataVoclass) throws Exception{
        Object[] obj=null;
        if(parmList!=null){
            obj=new Object[parmList.size()];
            for(int i=0;i<parmList.size();i++)
                obj[i]=parmList.get(i);
        }
        return query(sql, obj,dataVoclass);
    }
    public List query(String sql,Object[] parmObj,Class dataVoclass) throws Exception{
        List resultList=new ArrayList();
        PreparedStatement ps=createPreparedStatement(sql);
        if(parmObj!=null){
            for(int i=0;i<parmObj.length;i++)
                ps.setObject(i+1, parmObj[i]);
        }
        ResultSet rs=getResultSet(ps);
        try{
            resolveDataToClass(rs,resultList,dataVoclass);
        }catch(Exception  e){throw e;}finally{
            if(rs!=null)
                try{rs.close();}catch(Exception ee){ee.printStackTrace();}
        }
        return resultList;
    }
    public PageResult query2(String sql,List parmList,int size,int pageNo,Class dataVoclass) throws Exception{
        Object[] obj=null;
        if(parmList!=null){
            obj=new Object[parmList.size()];
            for(int i=0;i<parmList.size();i++)
                obj[i]=parmList.get(i);
        }
        return query(sql, obj,size,pageNo,dataVoclass);
    }
    public PageResult query(String sql,Object[] parmObj,int size,int pageNo,Class dataVoclass) throws Exception{
        List resultList=new ArrayList();
        String tempSql=sql.toLowerCase();
        tempSql="select count(*) from (" + tempSql+") t0";
        PreparedStatement ps=createPreparedStatement(tempSql);
        if(parmObj!=null){
            for(int i=0;i<parmObj.length;i++)
                ps.setObject(i+1, parmObj[i]);
        }
        ResultSet rs=getResultSet(ps);
        PageResult pr=new PageResult(size,pageNo);
        try{
            rs.next();
            int resultCount=Integer.parseInt(rs.getObject(1).toString());
            rs.close();
            ps.close();
            pr.setResultCount(resultCount);
            if(pageNo<=0)
                pageNo=1;
            int startindex=(pageNo-1)*size+1;
            tempSql=DataConversionFactory.getDataConversion(HibernateUtil.getDataBaseType(ftg)).buildPageSql(sql,startindex,(pageNo*size));
            PreparedStatement ps2=createPreparedStatement(tempSql);
            if(parmObj!=null){
                for(int i=0;i<parmObj.length;i++)
                    ps2.setObject(i+1, parmObj[i]);
            }
            rs=ps2.executeQuery();
            resolveDataToClass(rs,resultList,dataVoclass);
            pr.setList(resultList);
        }catch(Exception  e){throw e;}finally{
            if(rs!=null)
                try{rs.close();}catch(Exception ee){ee.printStackTrace();}
        }
        return pr;
    }
    private void resolveDataToClass(ResultSet rs,List resultList,Class dataVoclass) throws Exception{
        ResultSetMetaData rsmd = rs.getMetaData();
        Method[] methods=dataVoclass.getMethods();
        while(rs.next()){
            Object bean=dataVoclass.newInstance();
            for (int i=0;i<rsmd.getColumnCount();i++){
                String colname=rsmd.getColumnLabel(i+1);
                for(int f=0;f<methods.length;f++){
                    String m=methods[f].getName();
                    if(m.startsWith("set")){
                        m=m.replace("set", "");
                        if(colname.equalsIgnoreCase(m)){
                            try{
                                Object obj=rs.getObject(colname);
                                Object value=obj;
                                if(isLoadLob()){
                                    if (obj instanceof BLOB || obj instanceof Blob){
                                        if(obj!=null){
                                            BLOB bolb=(BLOB)obj;
                                            byte[] datab=StreamUtils.streamToByte(bolb.getBinaryStream());
                                            value=datab;
                                        }
                                    }else if(obj instanceof CLOB || obj instanceof Clob){
                                        if(obj!=null){
                                            CLOB clob=(CLOB)obj;
                                            String objv="";
                                            BufferedReader cb = new BufferedReader(clob.getCharacterStream());
                                            String str = "";
                                            while ((str = cb.readLine()) != null) {
                                                objv = objv.concat(str);  //最后以String的形式得到
                                            }
                                            value=objv;
                                        }
                                    }
                                }
                                if(value instanceof Date)
                                    value=rs.getTimestamp(colname);
                                BeanUtils.setValue(bean, m, value,methods[f].getParameterTypes()[0]);
                            }catch(Exception e){}
                            break;
                        }
                    }
                }
            }
            resultList.add(bean);
        }
    }

    public PageResult query2(String sql,List parmList,int size,int pageNo) throws Exception{
        Object[] obj=null;
        if(parmList!=null){
            obj=new Object[parmList.size()];
            for(int i=0;i<parmList.size();i++)
                obj[i]=parmList.get(i);
        }
        return query(sql, obj,size,pageNo);
    }
    public PageResult query(String sql,Object[] parmObj,int size,int pageNo) throws Exception{
        List resultList=new ArrayList();
        String tempSql=sql.toLowerCase();
        tempSql="select count(*) from (" + tempSql+") t0";
        PreparedStatement ps=createPreparedStatement(tempSql);
        if(parmObj!=null){
            for(int i=0;i<parmObj.length;i++)
                ps.setObject(i+1, parmObj[i]);
        }
        ResultSet rs=getResultSet(ps);
        PageResult pr=new PageResult(size,pageNo);
        try{
            rs.next();
            int resultCount=Integer.parseInt(rs.getObject(1).toString());
            rs.close();
            ps.close();
            pr.setResultCount(resultCount);
            if(pageNo<=0)
                pageNo=1;
            int startindex=(pageNo-1)*size+1;
            tempSql=DataConversionFactory.getDataConversion(HibernateUtil.getDataBaseType(ftg)).buildPageSql(sql,startindex,(pageNo*size));
            PreparedStatement ps2=createPreparedStatement(tempSql);
            if(parmObj!=null){
                for(int i=0;i<parmObj.length;i++)
                    ps2.setObject(i+1, parmObj[i]);
            }
            rs=ps2.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            while(rs.next()){
                JdataBean bean=new JdataBean();
                for (int i=0;i<rsmd.getColumnCount();i++){
                    //以大写字段名保存字段键值
                    Object obj=rs.getObject(rsmd.getColumnLabel(i+1).toUpperCase());
                    Object value=obj;
                    if(isLoadLob()){
                        if (obj instanceof BLOB || obj instanceof Blob){
                            if(obj!=null){
                                BLOB bolb=(BLOB)obj;
                                byte[] datab=StreamUtils.streamToByte(bolb.getBinaryStream());
                                value=datab;
                            }
                        }else if(obj instanceof CLOB || obj instanceof Clob){
                            if(obj!=null){
                                CLOB clob=(CLOB)obj;
                                String objv="";
                                BufferedReader cb = new BufferedReader(clob.getCharacterStream());
                                String str = "";
                                while ((str = cb.readLine()) != null) {
                                    objv = objv.concat(str);  //最后以String的形式得到
                                }
                                value=objv;
                            }
                        }
                    }
                    bean.put(rsmd.getColumnLabel(i+1),value);
                }
                resultList.add(bean);
            }
            pr.setList(resultList);

        }catch(Exception  e){throw e;}finally{
            if(rs!=null){
                try{rs.close();}catch(Exception ee){}
            }
        }
        return pr;
    }
    public TableBean getBean(Class classes,Serializable key) throws Exception{
        TableBean bean=null;
        try{
            bean=(TableBean)classes.newInstance();
        }catch(Exception ee){
            throw new Exception("数据持久类没有实现TableBean接口");
        }
        TableKey tbkey=bean.getTableKey();
        String sql="select * from "+tbkey.getTableName()+" where "+tbkey.getKeyCode()+"=?";
        PreparedStatement ps=createPreparedStatement(sql);
        ps.setObject(1,key);
        ResultSet rs=getResultSet(ps);
        try{
            ResultSetMetaData rsmd = rs.getMetaData();
            Field[] field=classes.getDeclaredFields();
            while(rs.next()){
                for (int i=0;i<rsmd.getColumnCount();i++){
                    String colname=rsmd.getColumnLabel(i+1);
                    for(int f=0;f<field.length;f++){
                        String fname=field[f].getName();
                        if(colname.equalsIgnoreCase(fname)){
                            try{
                                Object obj= rs.getObject(colname);
                                Object value=obj;
                                if(isLoadLob()){
                                    if (obj instanceof BLOB || obj instanceof Blob){
                                        if(obj!=null){
                                            BLOB bolb=(BLOB)obj;
                                            byte[] datab=StreamUtils.streamToByte(bolb.getBinaryStream());
                                            value=datab;
                                        }
                                    }else if(obj instanceof CLOB || obj instanceof Clob){
                                        if(obj!=null){
                                            CLOB clob=(CLOB)obj;
                                            String objv="";
                                            BufferedReader cb = new BufferedReader(clob.getCharacterStream());
                                            String str = "";
                                            while ((str = cb.readLine()) != null) {
                                                objv = objv.concat(str);  //最后以String的形式得到
                                            }
                                            value=objv;
                                        }
                                    }
                                }
                                if(value instanceof Date)
                                    value=rs.getTimestamp(colname);
                                BeanUtils.setValue(bean, fname,value,field[f].getType());
                            }catch(Exception e){}
                            break;
                        }
                    }

                }
            }
        }catch(Exception  e){throw e;}finally{
            if(rs!=null)
                try{rs.close();}catch(Exception ee){ee.printStackTrace();}
        }
        return bean;
    }
    public JdataBean getObject(String sql,Object[] obj) throws Exception{
        List<JdataBean> list=query(sql, obj);
        if(list.size()>0)
            return list.get(0);
        else
            return null;
    }

    public JdataBean getObject2(String sql,List parmList) throws Exception{
        Object[] objs=null;
        if(parmList!=null){
            objs=new Object[parmList.size()];
            for(int i=0;i<parmList.size();i++)
                objs[i]=parmList.get(i);
        }
        return getObject(sql,objs);
    }

    public void save(TableBean bean) throws Exception{
        Field[] field=bean.getClass().getDeclaredFields();
        TableKey key=bean.getTableKey();
        if(key.getTableName()==null || "".equals(key.getTableName()))
            throw new Exception("没有指定数据表名");
        String sql="insert into "+key.getTableName()+"(";
        String valuesql="";
        List parmList=new ArrayList();
        for(int f=0;f<field.length;f++){
            String fname=field[f].getName();
            Class type=field[f].getType();
            if(!checkDataType(type))
                continue;

            Object obj=BeanUtils.getValue(bean, fname);
            if(fname.equalsIgnoreCase(key.getKeyCode())){
                //获取主键
                Object keyvalue=BeanUtils.getValue(bean, key.getKeyCode());
                if(keyvalue==null){
                    keyvalue=new Seq().getKeyId(key);
                    BeanUtils.setValue(bean, fname, keyvalue);
                }else if(keyvalue.toString().equals("-999"))
                    keyvalue=null;

                obj=keyvalue;
            }
            if(obj!=null){
                if(!valuesql.equals("")){
                    sql+=",";
                    valuesql+=",";
                }
                sql+=fname;
                valuesql+="?";
                obj=DataConversionFactory.getDataConversion(HibernateUtil.getDataBaseType(ftg)).valueToDbData(obj, type);
                parmList.add(obj);
            }
        }
        sql+=") values ("+valuesql+")";
        PreparedStatement ps=createPreparedStatement(sql);
        for(int f=0;f<parmList.size();f++)
            ps.setObject(f+1, parmList.get(f));

        ps.executeUpdate();
    }
    public void update(TableBean bean) throws Exception{
        Field[] field=bean.getClass().getDeclaredFields();
        TableKey key=bean.getTableKey();
        if(key.getTableName()==null || "".equals(key.getTableName()))
            throw new Exception("没有指定数据表名");
        String sql="update "+key.getTableName();
        String setsql="";
        String keycol="";
        List parmlist=new ArrayList();
        for(int f=0;f<field.length;f++){
            String fname=field[f].getName();
            Class type=field[f].getType();
            if(!checkDataType(type))
                continue;
            if(!setsql.equals(""))
                setsql+=",";

            if(fname.equalsIgnoreCase(key.getKeyCode()))
                keycol=fname;
            else{
                setsql+=fname+" = ?";
                Object obj=BeanUtils.getValue(bean, fname);
                obj=DataConversionFactory.getDataConversion(HibernateUtil.getDataBaseType(ftg)).valueToDbData(obj, type);
                if(obj!=null && "".equals(obj.toString()))obj=null;
                parmlist.add(obj);
            }
        }
        sql+=" set "+setsql+" where "+keycol+" = ?";
        Object keyvalue=BeanUtils.getValue(bean, keycol);
        if(keycol.equals("") || keyvalue==null){
            throw new Exception("没有指定的主键!");
        }
        parmlist.add(keyvalue);
        PreparedStatement ps=createPreparedStatement(sql);
        for(int f=0;f<parmlist.size();f++)
            ps.setObject(f+1, parmlist.get(f));

        ps.executeUpdate();
    }
    public int update(String sql,Object[] obj) throws HibernateException, Exception{
        PreparedStatement ps=createPreparedStatement(sql);
        if(obj!=null){
            for(int i=0;i<obj.length;i++)
                ps.setObject(i+1,DataConversionFactory.getDataConversion(HibernateUtil.getDataBaseType(ftg)).valueToDbData(obj[i]));
        }
        return ps.executeUpdate();
    }
    public int update(String tableName,Map updateMap,Map keyMap) throws HibernateException, Exception{
        String setList = "";//更新字段列表
        String whereList = " and ";//条件字段列表
        Object[] keys1=null;
        int count1=0;
        if (updateMap==null || updateMap.size()==0){
            throw new Exception("没有要更新的字段和值！");
        }
        keys1 = updateMap.keySet().toArray();
        count1 = updateMap.size();
        for (int i = 0; i < count1; i++) {
            if (i < count1 - 1) {
                setList += keys1[i] + "=?,";
            } else {
                setList += keys1[i] + "=?";
            }
        }
        Object[] keys2=null;
        int count2=0;
        if (keyMap==null || keyMap.size()==0){
            whereList="";
        }else{
            keys2 = keyMap.keySet().toArray();
            count2 = keyMap.size();
            for (int i = 0; i < count2; i++) {
                if (i < count2 - 1) {
                    whereList += keys2[i] + "=? and ";
                } else {
                    whereList += keys2[i] + "=?";
                }
            }
        }
        String sql = "update " + tableName + " set " + setList + " where 1=1"
                + whereList;
        PreparedStatement pst=createPreparedStatement(sql);
        for (int i = 0; i < count1; i++) {
            Object obj=updateMap.get(keys1[i]);
            if(obj!=null && "".equals(obj.toString()))obj=null;
//            obj=obj==null?"":obj;
            if (obj instanceof BLOB){
                pst.setBlob(i+1,(BLOB)obj);
            }else if (obj instanceof CLOB){
                pst.setClob(i+1,(CLOB)obj);
            }else if (obj instanceof java.util.Date){
                java.util.Date date=(java.util.Date)obj;
                pst.setTimestamp(i+1, new Timestamp(date.getTime()));
            }else{
                pst.setObject(i + 1, obj);
            }
        }
        if (!whereList.equals("")){
            for (int i = 0; i < count2; i++) {
                Object obj=keyMap.get(keys2[i]);
                obj=obj==null?"":obj;
                pst.setObject(i + 1 + count1, obj);
            }
        }
        return pst.executeUpdate();
    }
    public void save(String tableName,Map dataMap) throws HibernateException, Exception{
        String colList = "";//更新字段列表
        String valueList = "";//条件字段列表
        Object[] keys1=null;
        int count1=0;
        if (dataMap==null || dataMap.size()==0){
            throw new Exception("没有要插入的字段和值！");
        }
        keys1 = dataMap.keySet().toArray();
        count1 = dataMap.size();
        for (int i = 0; i < count1; i++) {
            if(i!=0){colList+=",";valueList+=",";}
            colList += keys1[i];
            valueList += "?";
        }
        String sql = "insert into " + tableName + "(" + colList + ") values ("+valueList+")";
        PreparedStatement pst=createPreparedStatement(sql);
        for (int i = 0; i < count1; i++) {
            Object obj=dataMap.get(keys1[i]);
            if(obj!=null && "".equals(obj.toString()))obj=null;
//            obj=obj==null?"":obj;
            if (obj instanceof BLOB){
                pst.setBlob(i+1,(BLOB)obj);
            }else if (obj instanceof CLOB){
                pst.setClob(i+1,(CLOB)obj);
            }else if (obj instanceof java.util.Date){
                java.util.Date date=(java.util.Date)obj;
                pst.setTimestamp(i+1, new Timestamp(date.getTime()));
            }else{
                pst.setObject(i + 1, obj);
            }
        }
        pst.executeUpdate();
    }
    public String saveReturnKey(String tableName,Map dataMap) throws HibernateException, Exception{
        String colList = "";//更新字段列表
        String valueList = "";//条件字段列表
        Object[] keys1=null;
        int count1=0;
        if (dataMap==null || dataMap.size()==0){
            throw new Exception("没有要插入的字段和值！");
        }
        keys1 = dataMap.keySet().toArray();
        count1 = dataMap.size();
        for (int i = 0; i < count1; i++) {
            if(i!=0){colList+=",";valueList+=",";}
            colList += keys1[i];
            valueList += "?";
        }
        String sql = "insert into " + tableName + "(" + colList + ") values ("+valueList+")";
        PreparedStatement pst=createPreparedStatement(sql,Statement.RETURN_GENERATED_KEYS);
        for (int i = 0; i < count1; i++) {
            Object obj=dataMap.get(keys1[i]);
            if(obj!=null && "".equals(obj.toString()))obj=null;
//            obj=obj==null?"":obj;
            if (obj instanceof BLOB){
                pst.setBlob(i+1,(BLOB)obj);
            }else if (obj instanceof CLOB){
                pst.setClob(i+1,(CLOB)obj);
            }else if (obj instanceof java.util.Date){
                java.util.Date date=(java.util.Date)obj;
                pst.setTimestamp(i+1, new Timestamp(date.getTime()));
            }else{
                pst.setObject(i + 1, obj);
            }
        }
        pst.executeUpdate();
        ResultSet rs = pst.getGeneratedKeys();
        String id="";
        if (rs.next()) {
            //知其仅有一列，故获取第一列
            id =String.valueOf(rs.getLong(1));
        }
        return id;
    }
    public void delete(TableBean bean) throws HibernateException, Exception{
        TableKey tk=bean.getTableKey();
        String sql="delete "+tk.getTableName()+" where "+tk.getKeyCode()+"=?";
        PreparedStatement ps=createPreparedStatement(sql);
        ps.setObject(1, BeanUtils.getValue(bean, bean.getTableKey().getKeyCode()));
        ps.executeUpdate();
    }
    public int delete(String sql,Object[] obj) throws HibernateException, Exception{
        PreparedStatement ps=createPreparedStatement(sql);
        if(obj!=null){
            for(int i=0;i<obj.length;i++)
                ps.setObject(i+1,obj[i]);
        }
        return ps.executeUpdate();
    }

    public Session getSession() throws Exception{
        return HibernateUtil.currentSession(ftg);
    }
    public Connection getConnection() throws HibernateException, Exception{
        conn=getSession().connection();
        return conn;
    }
    public PreparedStatement createPreparedStatement(String sql) throws HibernateException, SQLException, Exception{
        PreparedStatement ps=getConnection().prepareStatement(sql);
        addPreparedStatement(ps);
        return ps;
    }
    public PreparedStatement createPreparedStatement(String sql,int type) throws HibernateException, SQLException, Exception{
        PreparedStatement ps=getConnection().prepareStatement(sql,type);
        addPreparedStatement(ps);
        return ps;
    }
    public Statement createStatement() throws HibernateException, SQLException, Exception{
        Statement st=getConnection().createStatement();
        addStatement(st);
        return st;
    }
    private ResultSet getResultSet(PreparedStatement ps) throws HibernateException, SQLException, Exception{
        ResultSet rs=ps.executeQuery();
        addResultSet(rs);
        return rs;
    }
    private ResultSet getResultSet(Statement st,String sql) throws HibernateException, SQLException, Exception{
        ResultSet rs=st.executeQuery(sql);
        addResultSet(rs);
        return rs;
    }
    private void addPreparedStatement(PreparedStatement ps){
        if(preparedStatementList==null)
            preparedStatementList=new ArrayList<PreparedStatement>();
        preparedStatementList.add(ps);
    }
    private void addStatement(Statement st){
        if(statementList==null)
            statementList=new ArrayList<Statement>();
        statementList.add(st);
    }
    private void addResultSet(ResultSet rs){
        if(resultSetList==null)
            resultSetList=new ArrayList<ResultSet>();
        resultSetList.add(rs);
    }
    public void closeStatements(){
        if(preparedStatementList!=null){
            for(int i=0;i<preparedStatementList.size();i++){
                PreparedStatement ps=preparedStatementList.get(i);
                if(ps!=null)
                    try {
                        ps.close();
                    } catch (SQLException e1) {
                    }
            }
            preparedStatementList.clear();
        }

        if(statementList!=null){
            for(int i=0;i<statementList.size();i++){
                Statement st=statementList.get(i);
                if(st!=null)
                    try {
                        st.close();
                    } catch (SQLException e1) {
                    }
            }
            statementList.clear();
        }
    }
    public void closeSession() {
        // TODO Auto-generated method stub
        closeStatements();
        try{
            HibernateUtil.closeSession(ftg);

        }catch(Exception e){
            e.printStackTrace();
        }
    }
    public void closeSession(boolean forcibly){
        closeStatements();
        try{
            if(forcibly)
                HibernateUtil.closeSession(ftg);
            else{
                if(!getSession().getTransaction().isActive()){
//                	System.out.print("--closeSession");
                    HibernateUtil.closeSession(ftg);
                }
            }
        }catch(Exception e){
            e.printStackTrace();
        }
    }
    private boolean checkDataType(Class c){
        boolean bl=false;
        if(c==String.class || c == Character.class || c == char.class)
            bl=true;
        else if(c == Long.class || c == long.class || c == Short.class || c == short.class || c==Integer.class || c == int.class)
            bl=true;
        else if(c == Float.class || c == float.class || c == Double.class || c == double.class)
            bl=true;
        else if(c == char[].class)
            bl=true;
        else if(c == byte[].class)
            bl=true;
        else if(c == Date.class || c == java.util.Date.class)
            bl=true;
        return bl;
    }

    public void beginTransaction() {
        try {
            getSession().beginTransaction();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }
    public Transaction getTransaction() throws Exception{
        return getSession().getTransaction();
    }

    @Override
    public List<Map> queryMap(String sql, Object[] parmObj) throws Exception {
        List resultList=new ArrayList();
        PreparedStatement ps=createPreparedStatement(sql);
        if(parmObj!=null){
            for(int i=0;i<parmObj.length;i++){
                if(parmObj[i] instanceof java.util.Date){
                    java.util.Date date=(java.util.Date)parmObj[i];
                    ps.setTimestamp(i+1, new Timestamp(date.getTime()));
                }else
                    ps.setObject(i+1, parmObj[i]);
            }
        }
        ResultSet rs=ps.executeQuery();
        try{
            ResultSetMetaData rsmd = rs.getMetaData();
            while(rs.next()){
                Map map=new LinkedHashMap();
                for (int i=0;i<rsmd.getColumnCount();i++){
                    String colname=rsmd.getColumnLabel(i+1);
                    Object obj=rs.getObject(colname);
                    Object value=obj;
                    if(isLoadLob()){
                        if (obj instanceof BLOB || obj instanceof Blob){
                            if(obj!=null){
                                BLOB bolb=(BLOB)obj;
                                value=StreamUtils.streamToByte(bolb.getBinaryStream());
                            }
                        }else if(obj instanceof CLOB || obj instanceof Clob){
                            if(obj!=null){
                                CLOB clob=(CLOB)obj;
                                String objv="";
                                BufferedReader cb = new BufferedReader(clob.getCharacterStream());
                                String str = "";
                                while ((str = cb.readLine()) != null) {
                                    objv = objv.concat(str);  //最后以String的形式得到
                                }
                                value=objv;
                            }
                        }
                    }
                    //以大写字段名保存字段键值
                    map.put(colname,value);
                }
                resultList.add(map);
            }
        }catch(Exception  e){throw e;}finally{
            if(rs!=null)
                try{rs.close();}catch(Exception ee){ee.printStackTrace();}
        }
        return resultList;
    }

    public void commit(){
        try {
            getSession().getTransaction().commit();
        } catch (HibernateException e1) {
            // TODO Auto-generated catch block
            e1.printStackTrace();
        } catch (Exception e1) {
            // TODO Auto-generated catch block
            e1.printStackTrace();
        }

    }
    public void rollback() throws SQLException{
        try {
            getSession().getTransaction().rollback();
        } catch (HibernateException e1) {
            // TODO Auto-generated catch block
            e1.printStackTrace();
        } catch (Exception e1) {
            // TODO Auto-generated catch block
            e1.printStackTrace();
        }
    }
    public String getFtg() {
        return ftg;
    }
    public void setFtg(String ftg) {
        this.ftg = ftg;
    }
    public boolean isLoadLob() {
        return loadLob;
    }
    public void setLoadLob(boolean loadLob) {
        this.loadLob = loadLob;
    }

}
